package com.sysware.p2m.oracleclient;

import com.sysware.p2m.oracleclient.mapper.DBUserMapper;
import com.sysware.p2m.oracleclient.mapper.DDLMapper;
import com.sysware.p2m.oracleclient.util.DBUtil;
import org.apache.ibatis.session.SqlSession;
import org.flywaydb.core.Flyway;
import org.flywaydb.core.internal.util.Location;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.util.HashMap;
import java.util.List;

public class OracleClient {

    private static Logger logger = LoggerFactory.getLogger(OracleClient.class);

    public OracleClient(String url, String username, String password) {
        this.setUrl(url);
        this.setUsername(username);
        this.setPassword(password);
    }

    /*
     * jdbc:oracle:thin:@//<host>:<port>/<service>
     */
    private String url;

    private String username;

    private String password;

    public static final String ORACLE_JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";

    private static final String[] xmlMapperResources = new String[]{
            "mapping/DDLMapper.xml", "mapping/DBUserMapper.xml"
    };

    /**
     * 创建用户、表空间、授权
     *
     * @return
     */
    public void createUser(String username, String password){
        SqlSession sqlSession = DBUtil.getMybatisSqlSession(ORACLE_JDBC_DRIVER, getUrl(), getUsername(), getPassword(),xmlMapperResources);
        DDLMapper ddlmapper = sqlSession.getMapper(DDLMapper.class);
        String userTablespacePath = ddlmapper.queryUserTablespacePath();
        String fileseparator = File.separator;
        if (userTablespacePath != null && userTablespacePath.length() > 0) {
            int p = userTablespacePath.lastIndexOf(fileseparator);
            if (p == -1) {
                fileseparator = "/";
                p = userTablespacePath.lastIndexOf(fileseparator);
            }
            if (p > 0)
                userTablespacePath = userTablespacePath.substring(0, p);
        }

        /**
         * 2、创建用户表空间
         */
        String tableSpaceName = username;
        ddlmapper.createTableSpace(tableSpaceName, userTablespacePath + fileseparator + username + ".DBF");

        DBUserMapper dbUserMapper = sqlSession.getMapper(DBUserMapper.class);

        /**
         * 3、创建用户
         */
        dbUserMapper.createUnlockUserWithTablespace(username, password, tableSpaceName);

        /**
         * 4.给用户授权
         */
        String[] roleList = new String[]{"CONNECT", "RESOURCE"};
        dbUserMapper.grantUserDBRoles(username, roleList);

        sqlSession.commit(true);
        sqlSession.close();
        logger.info("{} 创建成功!", username);
    }

    /**
     * 撤销用户权限、删除表空间、数据及用户
     *
     * @param userName
     */
    public void dropUser(String userName){
        SqlSession sqlSession = DBUtil.getMybatisSqlSession(ORACLE_JDBC_DRIVER, getUrl(), getUsername(), getPassword(),xmlMapperResources);
        DDLMapper ddlmapper = sqlSession.getMapper(DDLMapper.class);
        DBUserMapper dbUserMapper = sqlSession.getMapper(DBUserMapper.class);

        /**
         * 1、关闭该用户的所有会话
         */
        List<String> sessionList = dbUserMapper.querySessionByUser(userName);
        if (sessionList != null && !sessionList.isEmpty()) {
            for (String sessionId : sessionList) {
                if (sessionId != null && sessionId.length() > 0) {
                    dbUserMapper.killSession(sessionId);
                }
            }
            sqlSession.commit();
        }

        /**
         * 2.移除用户权限
         */
        List<String> roleList = dbUserMapper.getUserDBRoles(userName);
        if (roleList != null && !roleList.isEmpty()) {
            HashMap<String, Object> params = new HashMap<>();
            params.put("userName", userName);
            params.put("roleList", roleList);
            dbUserMapper.revokeUserDBRoles(params);
        }

        /**
         * 3、删除用户
         */
        dbUserMapper.dropUserCascade(userName);

        /**
         * 4、删除占用的表空间
         */
        ddlmapper.deleteTableSpace(userName);

        sqlSession.commit(true);
        sqlSession.close();
        logger.info("{} 删除成功", userName);
    }

    private Flyway flyway = new Flyway();
    private void setDataSource(){
        flyway.setDataSource(getUrl(), getUsername(), getPassword());
    }

    /**
     * 基于flyway执行指定目录下的sql文件
     *
     * @param sqlDirectory 需要执行的SQL目录
     * @param encoding     SQL文件编码
     * @throws RuntimeException
     * @author P2M.lixl
     * @version 2.9.6
     * @date 2018年10月17日下午8:22:59
     * @copyright Sysware Technology Co., Ltd
     */
    public void executeSQL(File sqlDirectory, String encoding){
        this.setDataSource();
        if (encoding != null && encoding.length() > 0) {
            flyway.setEncoding(encoding);// 默认编码UTF-8
        }
        flyway.setLocations(Location.FILESYSTEM_PREFIX + sqlDirectory.getAbsolutePath());
        flyway.setBaselineOnMigrate(true);
        try{
            flyway.repair();
        }catch (Exception e){
            logger.error(e.getMessage());
        }

        flyway.migrate();
        logger.info("{} 执行成功", sqlDirectory.getAbsolutePath());
    }

    /**
     * 执行指定目录下的sql文件
     *
     * @param sqlDirectory
     * @throws RuntimeException
     * @author P2M.lixl
     * @version 2.9.6
     * @date 2018年10月17日下午9:19:13
     * @copyright Sysware Technology Co., Ltd
     */
    public void executeSQL(File sqlDirectory) {
        this.executeSQL(sqlDirectory, null);
    }

    /**
     * 数据库基线
     * @param baselineVersion 基线版本
     * @param baselineDescription 说明
     * @throws RuntimeException
     */
    public void flywayBaseline(String baselineVersion, String baselineDescription){
        this.setDataSource();
        flyway.setBaselineVersionAsString(baselineVersion);
        flyway.setBaselineDescription(baselineDescription);
        flyway.baseline();
        logger.info("{} baseline {}成功", getUsername(), baselineVersion);
    }

    /**
     * 清空数据库
     * @throws RuntimeException
     */
    public void flywayClean(){
        this.setDataSource();
        flyway.clean();
        logger.info("数据库{} clean 完成", getUsername());
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

}
